﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class ShareDAL : IShareDAL
    {
        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];
        private const string SQL_SELECT_SHARE = "SELECT SH_shareId, SH_userId, SH_photoId, SH_time, SH_description FROM Share";
        private const string SQL_SELECT_SHARE1 = "SELECT SH_shareId, SH_userId, SH_photoId, SH_time, SH_description FROM Share WHERE SH_shareId = @shareId";
        private const string SQL_SELECT_SHARE2 = "SELECT SH_shareId, SH_userId, SH_photoId, SH_time, SH_description FROM Share WHERE SH_photoId = @photoId";
        private const string SQL_SELECT_SHARE3 = "SELECT SH_shareId, SH_userId, SH_photoId, SH_time, SH_description FROM Share WHERE SH_userId = @userId";
        private const string SQL_DELETE_SHARE = "DELETE FROM Share WHERE SH_shareId = @shareId";
        private const string SQL_DELETE_SHARE1 = "DELETE FROM Share WHERE SH_photoId = @photoId";
        private const string SQL_UPDATE_SHARE = "UPDATE Share SET SH_userId = @userId, SH_photoId = @photoId, SH_time = @time, SH_description = @description WHERE SH_shareId = @shareId";
        private const string SQL_INSERT_SHARE = "INSERT INTO Share VALUES(@userId, @photoId, @time, @description)";
        
        /// <summary>
        /// 根据shareId判断分享是否存在
        /// </summary>
        public bool shareExistByShareId(int shareId)
        {
            //Set up a return value
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@shareId", shareId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_SHARE1, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 根据photoId判断分享是否存在
        /// </summary>
        public bool shareExistByPhotoId(int photoId)
        {
            //Set up a return value
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_SHARE2, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 添加分享
        /// </summary>
        public bool Addshare(Share share)
        {
            //Set up a return value
            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", share.getSH_userId()), new SqlParameter("@photoId", share.getSH_photoId()), new SqlParameter("@time", share.getSH_time()), new SqlParameter("@description", share.getSH_description()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_SHARE, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

                /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 更新分享
        /// </summary>
        public bool updateShare(Share share)
        {
            //Set up a return value
            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", share.getSH_userId()), new SqlParameter("@photoId", share.getSH_photoId()), new SqlParameter("@time", share.getSH_time()), new SqlParameter("@description", share.getSH_description()), new SqlParameter("@shareId", share.getSH_shareId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_SHARE, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

                /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 根据shareId删除分享
        /// </summary>
        public bool deleteShareByShareId(int shareId)
        {
            //Set up a return value
            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@shareId", shareId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_DELETE_SHARE, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

                /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 根据photoId删除分享
        /// </summary>
        public bool deleteShareByPhotoId(int photoId)
        {
            
             //Set up a return value
            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_DELETE_SHARE1, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

                /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 获取share对象
        /// </summary>
        public Model.Share getModel(int shareId)
        {
            Model.Share share = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@shareId", shareId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_SHARE1, para))
                {
                    if (rdr.Read())

                        share = new Model.Share(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToDateTime(rdr.GetDateTime(3)), rdr.GetString(4));
                    else
                        share = new Model.Share();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return share;
        }


        /// <summary>
        /// 根据userId获取所有分享对象
        /// </summary>
        public List<Model.Share> getModelByUserId(int userId)
        {
            List<Model.Share> list = new List<Model.Share>();

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_SHARE3, para);

                while (rdr.Read())
                {
                    Model.Share shareModel;
                    shareModel = new Model.Share(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToDateTime(rdr.GetDateTime(3)), rdr.GetString(4));

                    list.Add(shareModel);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        
        }


        /// <summary>
        /// 根据photoId获取所有分享对象
        /// </summary>
        public List<Share> getModelByPhotoId(int photoId)
        {
            List<Model.Share> list = new List<Model.Share>();

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_SHARE2, para);

                while (rdr.Read())
                {
                    Model.Share shareModel;
                    shareModel = new Model.Share(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToDateTime(rdr.GetDateTime(3)), rdr.GetString(4));

                    list.Add(shareModel);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }
    }
}
